<?php
/**
 * dbfront
 * (c) 2010-2013 Stephen Adkins <spadkins@gmail.com>
 * This software began development in 2010.
 * It is based on code dating from 1999.
 *
 * License: GPL 2.0
 * The contents of this file may be used under the terms of
 * the GNU General Public License Version 2 or later (the "GPL").
 *
 * Software distributed under the License is distributed on an "AS IS" basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
 * for the specific language governing rights and limitations under the
 * License.
 * */

/**
 * FILE: extjs-grid.php
 * This file is designed as the handler for all Ajax calls to support the ExtJS 4.1.X Ext.Grid.GridPanel component.
 * The following are the parameters recognized.
 *     u               - the username                     (required on all queries)  **FUTURE/NOT YET IMPLEMENTED**
 *     p               - the password                     (required on all queries)  **FUTURE/NOT YET IMPLEMENTED**
 *     database        - the database (a connection)      (required on all queries)
 *     schema          - the schema                       (required on all queries)
 *     table           - the table to operate on          (required on all queries)
 *     columns         - comma-separated list of columns  (required on all queries)
 *     start           - the first row number of the query data to be returned to the client (0 is the first row of the result set) (skip over rows before this one and don't return them)
 *                                                        (this is technically optional, but it is sensible to configure your Ext program to always send this value for result set paging)
 *     limit           - the maximum number of rows of the query data to be returned to the client
 *                                                        (this is technically optional, but it is sensible to configure your Ext program to always send this value for result set paging)
 *     sort            - json-encoded array of sort columns (e.g. [{"property":"alt_city_nm","direction":"ASC"}])
 *     rowtype         - "array" or "object" (default is "array") specifies the format of the returned data (either will work with Ext.Grid)
 *     primary_key     - comma-separated list of columns that make up the primary key (for updating)
 *     p-{column}      - filter (where clause) parameters (implied =/IN operation)
 *     p-{column}-{op} - filter (where clause) parameters (explicit operation)
 *     neat            - used for debugging, this puts extra newlines
 *     nocount         - inhibit the extra query that produces the totalCount element used for Grid paging
 * */

header('Content-type: text/plain');

require_once('frontstart.inc.php');   # defines: $options[], $context

class Application {
    function handle_request ($options) {
        try {
            if (! isset($_REQUEST['database']) && !$_REQUEST['database']) throw new \Exception('ERROR: the "database" parameter must be supplied');
            $database  = $_REQUEST['database'];
            if (! preg_match('/^[a-zA-Z0-9_]+$/', $database)) throw new \Exception('ERROR: the "database" parameter contains illegal characters (must be letters, digits, and underscores)');
            
            if (! isset($_REQUEST['schema']) && !$_REQUEST['schema']) throw new \Exception('ERROR: the "schema" parameter must be supplied');
            $schema   = $_REQUEST['schema'];
            if (! preg_match('/^[a-zA-Z0-9_]+$/', $schema)) throw new \Exception('ERROR: the "schema" parameter contains illegal characters (must be letters, digits, and underscores)');
            
            $sql = null;
            if (isset($_REQUEST['sql']) && $_REQUEST['sql']) {
                $sql = $_REQUEST['sql'];
            }
            else {
                if (! isset($_REQUEST['table']) || !$_REQUEST['table']) throw new \Exception('ERROR: the "table" parameter must be supplied');
                $table   = $_REQUEST['table'];
                if (! preg_match('/^[a-zA-Z0-9_]+$/', $table)) throw new \Exception('ERROR: the "table" parameter contains illegal characters (must be letters, digits, and underscores)');
            
                if (! isset($_REQUEST['columns'])) throw new \Exception('ERROR: the "columns" parameter must be supplied');
                $columns = $_REQUEST['columns'];
                if (! preg_match('/^[a-zA-Z0-9_,]+$/', $columns)) throw new \Exception('ERROR: the "columns" parameter contains illegal characters (columns must be letters, digits, and underscores, separated by commas)');

                $visible_columns   = (isset($_REQUEST['visible_columns'])   && $_REQUEST['visible_columns'])   ? $_REQUEST['visible_columns'] : null;
                if ($visible_columns && ! preg_match('/^[a-zA-Z0-9_,]+$/', $visible_columns)) throw new \Exception('ERROR: the "visible_columns" parameter contains illegal characters (visible_columns must be letters, digits, and underscores, separated by commas)');
            }

            $context = App::context($options);
            $db = $context->database($database);
            $matches = Array();

            $results = Array();
            $results['success'] = true;   # assume we are going to succeed, whatever it is we are doing

            ###################################################################################################
            # Perform Actions
            ###################################################################################################

            if (isset($_REQUEST['record'])) {
                # do nothing. not sure what this is for.
                throw new \Exception('ERROR: the "record" parameter was detected and we dont know what to do with that');
            }
            elseif (isset($_REQUEST['add'])) {
                $json = str_replace('\\"','"',$_REQUEST['add']);
                $record = json_decode($json, true);
                throw new \Exception('ERROR: the "add" parameter is not yet implemented');
            }
            elseif (isset($_REQUEST['modifiedRecord'])) {
                $json = str_replace('\\"','"',$_REQUEST['modifiedRecord']);
                $record = json_decode($json, true);
                throw new \Exception('ERROR: the "modifiedRecord" parameter is not yet implemented');
            }
            elseif (isset($_REQUEST['deleteRecord'])) {
                $json = str_replace('\\"','"',$_REQUEST['deleteRecord']);
                $record = json_decode($json, true);
                throw new \Exception('ERROR: the "deleteRecord" parameter is not yet implemented');
            }
            elseif (isset($sql)) {

                ###################################################################################################
                # Data Query
                ###################################################################################################

                $nocount   = (isset($_REQUEST['nocount'])   && $_REQUEST['nocount'])   ? 1 : 0;
                $neat      = (isset($_REQUEST['neat'])      && $_REQUEST['neat'])      ? 1 : 0;

                if (isset($_REQUEST['rowtype'])) {
                    $rowtype = $_REQUEST['rowtype'];
                    if ($rowtype != 'array' && $rowtype != 'object') throw new \Exception('ERROR: the "rowtype" parameter is an illegal value (must be "array" or "object")');
                }
                else {
                    $rowtype = 'array';
                }

                ###################################################################################################
                # Query Options
                ###################################################################################################

                $get_options = array();

                if (isset($_REQUEST['start']) && $_REQUEST['start'] > 0) {
                    $start = $_REQUEST['start'];
                    if (! preg_match('/^[0-9]+$/', $start)) throw new \Exception('ERROR: the "start" parameter contains illegal characters (must be a non-negative integer)');
                    $get_options['offset'] = $start;
                }
                if (isset($_REQUEST['limit'])) {
                    $limit = $_REQUEST['limit'];
                    if (! preg_match('/^[0-9]+$/', $limit)) throw new \Exception('ERROR: the "limit" parameter contains illegal characters (must be a positive integer)');
                    if ($limit <= 0) throw new \Exception('ERROR: the "limit" parameter must be a positive integer');
                    $get_options['limit'] = $limit;
                }

                if (isset($options['test_mode']) && $options['test_mode']) {
                    if (isset($_REQUEST['debug_sql'])) {
                        if (! preg_match('/^-?[0-9]+$/', $_REQUEST['debug_sql'])) throw new \Exception('ERROR: the "debug_sql" parameter contains illegal characters (must be an integer)');
                        global $debug_sql;
                        $debug_sql = $_REQUEST['debug_sql'];
                    }
                    if (isset($_REQUEST['trace'])) {
                        if (! preg_match('/^-?[0-9]+$/', $_REQUEST['trace'])) throw new \Exception('ERROR: the "trace" parameter contains illegal characters (must be an integer)');
                        global $trace;
                        $trace = $_REQUEST['trace'];
                    }
                }

                if ($rowtype == 'array') {
                    $data = $db->get_rows($sql, null, null, $get_options);
                }
                else {
                    $data = $db->get_hashes($sql, null, null, $get_options);
                }
                $results['data'] = $data;
                $results['sql']  = $sql;

                if (!$nocount) {
                    $count_options = $get_options;
                    if (isset($count_options['offset'])) {
                        $count_options['limit'] = $count_options['offset'] + 1000;
                        unset($count_options['offset']);
                    }
                    else {
                        $count_options['limit'] = 1000;
                    }
                    $sql = preg_replace('/[ \\t\\n]+order +by.*/is', '', $sql);

                    $db->add_limit_clause($sql, $count_options);
                    $count_rows = $db->get_rows("select count(*) from ($sql) x");
                    $count = $count_rows[0][0];

                    $results['total'] = $count;
                }
            }
            else {

                ###################################################################################################
                # Data Query
                ###################################################################################################

                $nocount   = (isset($_REQUEST['nocount'])   && $_REQUEST['nocount'])   ? 1 : 0;
                $neat      = (isset($_REQUEST['neat'])      && $_REQUEST['neat'])      ? 1 : 0;

                if (isset($_REQUEST['rowtype'])) {
                    $rowtype = $_REQUEST['rowtype'];
                    if ($rowtype != 'array' && $rowtype != 'object') throw new \Exception('ERROR: the "rowtype" parameter is an illegal value (must be "array" or "object")');
                }
                else {
                    $rowtype = 'array';
                }

                ###################################################################################################
                # Start with the Base Params and Add the User-Selected Dynamic Filters to the Params
                ###################################################################################################

                $params      = Array();

                foreach ($_REQUEST as $var => $value) {
                    if (preg_match('/^p-([a-zA-Z0-9_-]+)$/', $var, $matches)) {
                        $params[$matches[1]] = $value;
                        #echo "param: $matches[1] = $value\n";
                    }
                }

                if (isset($_REQUEST['filter']) && $_REQUEST['filter']) {
                    $json = str_replace('\\"','"',$_REQUEST['filter']);
                    $filters = json_decode($json, true);
                    #echo "FILTERS: $filters\n";
                    foreach ($filters as $filter) {
                        #echo "FILTER: ", $db->assoc_as_string($filter), "\n";
                        if (isset($filter['field'])) {
                            $field = $filter['field'];
                            if (isset($filter['type']) &&
                                isset($filter['comparison']) &&
                                isset($filter['value'])) {
                                $type       = $filter['type'];
                                $comparison = $filter['comparison'];
                                $value      = $filter['value'];
                                if (($comparison == 'lt' || $comparison == 'gt') && $type == 'numeric') {
                                    $params["$field-$comparison"] = $value;
                                    # echo "FILTER: field=[$field] type=[$type] comparison=[$comparison] value=[$value] : params[$field-contains]=$value\n";
                                }
                                elseif ($comparison == 'eq' || $type == 'string') {
                                    $params[$field] = $value;
                                    # echo "FILTER: field=[$field] type=[$type] comparison=[$comparison] value=[$value] : params[$field]=$value\n";
                                }
                            }
                            elseif (isset($filter['type']) &&
                                    isset($filter['value'])) {
                                $type       = $filter['type'];
                                $value      = $filter['value'];
                                if (preg_match('/^[A-Za-z \\.]+$/', $value)) {
                                    $params["$field-contains"] = $value;
                                    #echo "FILTER: field=[$field] type=[$type] value=[$value] : params[$field-contains]=$value\n";
                                }
                                else {
                                    $params[$field] = $value;
                                    #echo "FILTER: field=[$field] type=[$type] value=[$value] : params[$field]=$value\n";
                                }
                            }
                        }
                    }
                }

                ###################################################################################################
                # Columns
                ###################################################################################################

                $get_options = array();

                if (isset($_REQUEST['aggregate']) && $_REQUEST['aggregate'] && $_REQUEST['aggregate'] != 'false') {
                    $get_options['aggregate'] = 1;
                    if (isset($visible_columns))  {
                        $columns_array = preg_split('/,/', $visible_columns);   # convert from a comma-separated list to an actual PHP array
                    }
                    else {
                        $columns_array = preg_split('/,/', $columns);   # convert from a comma-separated list to an actual PHP array
                    }
                }
                else {
                    $columns_array = preg_split('/,/', $columns);   # convert from a comma-separated list to an actual PHP array
                }

                ###################################################################################################
                # Query Options
                ###################################################################################################

                if (isset($_REQUEST['sort']) && $_REQUEST['sort']) {
                    #echo "sort=[$_REQUEST[sort]]\n";
                    $sort_param = $_REQUEST['sort'];
                    if ($sort_param[0] == '[') {   # ]
                        # for some reason, json_decode on PHP 5.3.5 on Mac doesn't decode [{\"var\":\"value\"}]
                        # so we need the following line to get rid of the escaped quotes
                        $sort_param = str_replace('\\"', '"', $sort_param);
                        $sorts = json_decode($sort_param, true);
                        if (is_array($sorts) && count($sorts) > 0) {
                            $order_by_columns = Array();
                            foreach ($sorts as $sort) {
                                if (isset($sort['property']) && $sort['property']) {
                                    if (isset($sort['direction'])) {
                                        $direction = $sort['direction'];
                                        if (preg_match('/^(ASC|DESC)$/i', $direction)) {
                                            $order_by_columns[] = "$sort[property].$direction";
                                        }
                                        else {
                                            throw new \Exception('ERROR: the "direction" in a "sort" is not recognized (not "ASC" or "DESC") ($_REQUEST[sort])');
                                        }
                                    }
                                    else {
                                        $order_by_columns[] = "$sort[property]";
                                    }
                                }
                            }
                            $get_options['order_by'] = $order_by_columns;
                        }
                    }
                    else {
                        $sorts = explode(',', $sort_param);
                        if (is_array($sorts) && count($sorts) > 0) {
                            $order_by_columns = Array();
                            foreach ($sorts as $sort) {
                                if (preg_match('/^([A-Za-z0-9_]+)\\.(asc|desc)$/i', $sort, $matches)) {
                                    $order_by_columns[] = $matches[1] . '.' . strtolower($matches[2]);
                                }
                                elseif (preg_match('/^[A-Za-z0-9_]+$/', $sort, $matches)) {
                                    $order_by_columns[] = $sort;
                                }
                                else {
                                    throw new \Exception('ERROR: not a valid sort spec ($sort) (must be a column name, optionally followed by ".asc" or ".desc")');
                                }
                            }
                            $get_options['order_by'] = $order_by_columns;
                        }
                    }
                }

                if (isset($_REQUEST['start']) && $_REQUEST['start'] > 0) {
                    $start = $_REQUEST['start'];
                    if (! preg_match('/^[0-9]+$/', $start)) throw new \Exception('ERROR: the "start" parameter contains illegal characters (must be a non-negative integer)');
                    $get_options['offset'] = $start;
                }
                if (isset($_REQUEST['limit'])) {
                    $limit = $_REQUEST['limit'];
                    if (! preg_match('/^[0-9]+$/', $limit)) throw new \Exception('ERROR: the "limit" parameter contains illegal characters (must be a positive integer)');
                    if ($limit <= 0) throw new \Exception('ERROR: the "limit" parameter must be a positive integer');
                    $get_options['limit'] = $limit;
                }

                if (isset($options['test_mode']) && $options['test_mode']) {
                    if (isset($_REQUEST['debug_sql'])) {
                        if (! preg_match('/^-?[0-9]+$/', $_REQUEST['debug_sql'])) throw new \Exception('ERROR: the "debug_sql" parameter contains illegal characters (must be an integer)');
                        global $debug_sql;
                        $debug_sql = $_REQUEST['debug_sql'];
                    }
                    if (isset($_REQUEST['trace'])) {
                        if (! preg_match('/^-?[0-9]+$/', $_REQUEST['trace'])) throw new \Exception('ERROR: the "trace" parameter contains illegal characters (must be an integer)');
                        global $trace;
                        $trace = $_REQUEST['trace'];
                    }
                }

                $get_options['sql'] = 1;
 
                if ($rowtype == 'array') {
                    $data = $db->get_rows("$schema.$table", $params, $columns_array, $get_options);
                }
                else {
                    $data = $db->get_hashes("$schema.$table", $params, $columns_array, $get_options);
                }
                $results['data'] = $data;
                $results['sql']  = $get_options['sql'];

                if (!$nocount) {
                    $count_options = $get_options;
                    if (isset($count_options['offset'])) {
                        $count_options['limit'] = $count_options['offset'] + 1000;
                        unset($count_options['offset']);
                    }
                    else {
                        $count_options['limit'] = 1000;
                    }
                    if (isset($count_options['order_by'])) unset($count_options['order_by']);

                    $count_sql  = $db->make_select_sql("$schema.$table", $params, Array('1 as num'), $count_options);
                    $count_rows = $db->get_rows("select count(*) from ($count_sql) x");
                    $count = $count_rows[0][0];

                    $results['total'] = $count;
                }
            }
            
            if ( isset($_REQUEST['format']) && $_REQUEST['format'] !== 'json') {
                
                if ( !isset($_REQUEST['fname']) || preg_match("/\.\./",$_REQUEST['fname'])) throw new \Exception("Invalid File Name");
                
                $file_base  = ( isset($_REQUEST['fname']) && strlen($_REQUEST['fname']) > 0) ? $_REQUEST['fname'] : date("d_m_Y-H:i:s");
                $file_name  = $file_base . ".{$_REQUEST['format']}";
                $file_dir   = $options['user_document_root'] . "/" . $_REQUEST['to'];
                $file_url   = preg_replace("/\/\//","/", $file_dir . "/" . $file_name);
                $file_count = 1;
                
                while( is_file($file_url) && !( isset($_REQUEST['overwrite']) && $_REQUEST['overwrite'] == 1)) {
                    $file_name = $file_base . "-" . $file_count . ".{$_REQUEST['format']}";
                    $file_url = preg_replace("/\/\//","/", $file_dir . "/" . $file_name);
                    $file_count += 1;
                    echo "\n";
                    print_r($file_url);
                    
                }
                
                $fh = fopen($file_url, 'w');
                
                if (!$fh) 
                    throw new \Exception("ERROR: could not open file at $file_name");
                elseif ( preg_match("/^CSV$/i", $_REQUEST['format']) )
                    $this->convert_data_to_csv($fh, $results['data']);
                elseif (preg_match("/^XLS[x]$/i", $_REQUEST['format'])) 
                    $this->convert_data_to_xls($fh, $results['data']);
                else 
                    throw new \Exception("Unkown file format [" . $_REQUEST['format'] . "]" );
                
                unset($results);
                $results['success'] = true;
                $results['msg'] = "File " . $file_name . " has been successfully created";
            }
            
            $json = json_encode($results);

            if ($neat) {
                $json = preg_replace('/\\},\\{/', "},\n {", $json);
                 $json = preg_replace('/\\],\\[/', "],\n [", $json);
                 $json = preg_replace('/,"sql":/', ",\n".'"sql":', $json);
            }

            echo "{$json}\n";
        }
        catch (Exception $e) {
            $this->return_json_error($e->getMessage());
        }
        return(null);
    }

    function return_json_error ($message) {
        if (preg_match("/\n/",$message)) {
            $message = preg_replace("/ *\n */"," ",$message);
        }
        if (preg_match("/\\'/",$message)) {
            $message = preg_replace("/\\'/","\\'",$message);
        }
        echo "{\n";
        echo "    'success': false,\n";
        echo "    'msg': '$message'\n";
        echo "}\n";
    }

    function convert_data_to_csv ($fh, $results) {
        global $options;
        $delimiter       = (isset($_REQUEST['delimiter']) ? '\'' . $_REQUEST['delimiter'] . '\'' : ',');
        $columns         = preg_split('/,/',$_REQUEST['columns']);
        $delimiterStrLen = strlen($delimiter);
        fwrite($fh,implode($delimiter,$columns) . "\n");
        //for($i = 0; $i < $results['total']; $i++){
        for($i = 0; $i < count($results); $i++){
            $record_string = "";
            foreach ($columns as $column) {
                $cell = $results[$i][$column];
                // if cell contains delimiter we need to put it in quotes and escape quotes within the cell
                if (preg_match( $delimiter , $cell )) {
                    // TODO: test this 
                    $cell = str_replace($cell,'\"','"');
                    $record_string .= "\"{$cell}\"{$delimiter}";
                }
                else {
                    $record_string .= "{$cell}{$delimiter}";
                }
            }
            fwrite($fh,substr($record_string,0,(strlen($record_string) - $delimiterStrLen)) . "\n");
        }
    }
    
    function convert_data_to_xls ($fh, $results) {
        global $options;
        require_once($options['utils_root'] . "php/excel/PHPExcel.php");
        $objPHPExcel = new PHPExcel();
        echo "php excel object created\n";
        $columns   = preg_split(",",$_REQUEST['columns']);
        for($i = 0; $i < count($results); $i++){
            foreach ($columns as $column) {
                $cell = $results[$i][$column];
            }
        }
    }
}    

$application = new Application();
$application->handle_request($options);

